from flask import current_app as app,jsonify,make_response
from flask import Blueprint,request
from db import db1
import os
from utils.myfile import myf
from xlrd import open_workbook
bp_company = Blueprint("company",__name__)
import time
import functools
#计算接口运行时间
def timer(func):
    @functools.wraps(func)
    def wrapper(*args,**kargs):
        stime = time.time()
        func(*args,**kargs)
        endtime = time.time()
        t = endtime-stime
        print('执行了'+str(t)+'秒')
        return  func(*args,**kargs)
    return wrapper

#上传excel，并导入数据库
@bp_company.route('/upload',methods=['GET'])
@timer
def upload():
    time.sleep(1)
    return 'ok'
    #获取文件
    # file = request.files['file']
    # newname = myf.rename(file.filename)
    # #上传文件
    # file.save(os.path.join('./static/files/'+newname))
    #打开文件读取
    newname = '6579f04401c542788af3d97e87a61d7c.xls'
    name = './static/files/'+newname
    bk = open_workbook(name,encoding_override='utf-8')
    sh = bk.sheets()[0]
    nrows = sh.nrows
    ncols = sh.ncols
    for i in range(2,nrows):
        row_date = sh.row_values(i)
        for j in range(0,ncols):
            print(row_date[j])
         
    return 'ok'
    #封装数据写入数据库

# def timer(func):
#     @functools.wraps(func)
#     def decor(*args):
#         start_time = time.time()
#         func(*args)
#         end_time = time.time()
#         d_time = end_time - start_time
#         print("the running time is : ", d_time)
#         return  func(*args)
#     return decor
import asyncio


def writetest(sql):
    time.sleep(1)
    db1.update(sql)
    return 'ok'
        
# @bp_company.route('/test')
# async def test():
#     s = time.time()
#     loop = asyncio.get_event_loop()
#     task=[]
#     for i in range(10):
#         name = 'asfasfs柑顶起村地霜'+str(i)
#         sql = "insert into test values(0,'%s')"%(name)
      
#         task.append(sql)
#     tasks =[writetest(i) for i in task]
    
#     loop.run_until_complete(asyncio.wait(tasks))
#     loop.close()
#     end = time.time()
#     t = end-s
#     return jsonify({"code":200,'mes':t})

# from pandas import DataFrame
# @bp_company.route('/test1')
# async def test1():
#     data={'name':['a','b','李'],'age':[1,2,4]}
#     df = DataFrame(data)
#     df.to_excel('static/files/2.xlsx')


# from io import BytesIO
# from xlrd import open_workbook
# from urllib.parse import quote
# import xlsxwriter
# import datetime
# import mimetypes


# @bp_company.route('/download',methods=["GET","POST"])
# def download():
#      # 实例化二进制数据
#     out = BytesIO()
#     # 创建一个Excel实例
#     workbook = xlsxwriter.Workbook(out)
#     # 添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
#     table = workbook.add_worksheet()
#     # table.write(行, 列, 字段名)
#     # 行、列从 0 开始计数
#     # 字段命名根据每个人使用情况而定，插入数据部分也是
#     table.write(0, 0, "资产编号")
#     table.write(0, 1, "资产名称")
   
#     workbook.close()
#     filename = quote("资产列表_{}.xlsx".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
#     file = make_response(out.getvalue())
#     out.close()
#     mime_type = mimetypes.guess_type('%s.xlsx' % filename)[0]
#     file.headers['Content-Type'] = mime_type
#     file.headers["Cache-Control"] = "no-cache"
#     file.headers['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(filename)
#     return file

from io import BytesIO
from xlrd import open_workbook
from urllib.parse import quote
import xlsxwriter
import datetime
import mimetypes

@bp_company.route('/download',methods=["GET","POST"])
@timer
def download():
     # 实例化二进制数据
    out = BytesIO()
    # 创建一个Excel实例
    workbook = xlsxwriter.Workbook(out)
    # 添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
    table = workbook.add_worksheet()
    # table.write(行, 列, 字段名)
    # 行、列从 0 开始计数
    # 字段命名根据每个人使用情况而定，插入数据部分也是
    sql = "select * from test limit 0,10"
    res = db1.find_all(sql)
    table.write(0, 0, "编号")
    table.write(0, 1, "名称")
    num =1
    for i in res:
        table.write(num, 0, i['id'])
        table.write(num, 1, i['name'])
        num+=1
   
    workbook.close()
    filename = quote("资产列表_{}.xls".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
    file = make_response(out.getvalue())
    out.close()
    mime_type = mimetypes.guess_type('%s.xls' % filename)[0]
    file.headers['Content-Type'] = mime_type
    file.headers["Cache-Control"] = "no-cache"
    file.headers['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(filename)
    return file

# from utils.ess import Myes

# @bp_company.route('/estestinsert')
# def estest():
#     es = Myes('testtabel','test-type')
#     try:
#         sql = "select * from test limit 0,10"
#         data = db1.find_all(sql)
#         j=-1
#         for i in data:
#             j+=1
#             es.insert(i)
#         return 'true'
#     except:
#         print('error')
#         return 'false'
    

# import json
# @bp_company.route('/esget')
# def esget():
#     es = Myes('testtabel','test-type')
#     body = {
#         #    "match":{
#         #     "name":"abc"
#         # }
#         "multi_match":{
#             "query":"abc",
#             "fields":["name", "content"]
#         }
#     }
#     data = es.search(body)
#     address_data = data['hits']['hits']
#     print(address_data)
#     return 'ok'
    
import copy
def xtree(data):
    list=[]
    if len(data) <=0:
        return data

    #先把data封装成字典
    dict = {}
    for i in data:
        i['label']=i['name']
        dict[i['id']]=i
    #二次遍历
    for j in data:
        #判断父id是否为0
        pid = j['pid']
        j['label']=j['name']
        if pid == 0:
            list.append(j)
        else:
            #子类
            #看父类下有没有元素，如果已经有，直接加入到列表中
            #如果没有创建空列表
            if 'children' not in dict[pid]:
                dict[pid]['children']=[]
            dict[pid]['children'].append(j)
    return list

    

@bp_company.route('/deptlist')
def deptlist():
    sql = "select * from dept"
    res = db1.find_all(sql)
    list = xtree(res)
    return jsonify({"code":200,'list':list})

@bp_company.route('/test')
def test():
    return 'oks'
from utils.es import Es
@bp_company.route('/deptesinsert')
def deptesinsert():
    es = Es('testes','test-type')
    #插入数据
    sql = 'select * from test limit 0,10'
    res = db1.find_all(sql)
    for i in res:
        es.insert(i)
    return 'ok'

@bp_company.route('/getdata')
def getdata():
    es = Es('testes','test-type')
    query={
        "multi_match":{
            "query":"abc",
            "fields":["name", "content"]
        }
    }
    #插入数据
    data = es.find(query)
    mes = data['hits']['hits']
    print(mes)

    return 'ok'


        